"""
随机书籍获取操作类 - 高性能版本
用于按不同策略获取随机书籍信息
"""

import aiomysql
import random
from typing import List, Dict, Any, Optional
from db import DatabaseConnection, db_connection


class RandomBookOperations:
    """随机书籍获取操作类 - 高性能优化版本"""

    def __init__(self):
        self.db_connection: DatabaseConnection = db_connection
        # 缓存候选池，避免每次都查询
        self._hot_books_cache = []
        self._high_rating_books_cache = []
        self._cache_timestamp = 0
        self._cache_duration = 3600  # 缓存1小时

    async def get_random_books_by_strategy(
        self,
        total_count: int,
        hot_ratio: float = 0.1,
        high_rating_ratio: float = 0.1,
        random_ratio: float = 0.8,
        min_rating_count: int = 1000,
        min_rating_score: float = 8.0,
        exclude_book_ids: Optional[List[int]] = None,
    ) -> List[Dict[str, Any]]:
        """
        高性能随机获取书籍 - 单次查询优化版本
        """
        import time

        current_time = time.time()

        # 检查缓存是否需要更新
        if current_time - self._cache_timestamp > self._cache_duration:
            await self._refresh_cache(min_rating_count, min_rating_score)
            self._cache_timestamp = current_time

        # 计算各类别数量
        hot_count = int(total_count * hot_ratio)
        high_rating_count = int(total_count * high_rating_ratio)
        random_count = total_count - hot_count - high_rating_count

        exclude_set = set(exclude_book_ids or [])
        all_books = []
        used_book_ids = set()

        try:
            # 1. 从缓存中获取热门书籍
            if hot_count > 0 and self._hot_books_cache:
                available_hot = [
                    b
                    for b in self._hot_books_cache
                    if b["book_id"] not in exclude_set
                    and b["book_id"] not in used_book_ids
                ]
                selected_hot = random.sample(
                    available_hot, min(hot_count, len(available_hot))
                )

                # 获取完整信息
                hot_books = await self._get_books_with_categories(
                    [b["book_id"] for b in selected_hot]
                )
                all_books.extend(hot_books)
                used_book_ids.update(b["book_id"] for b in hot_books)

            # 2. 从缓存中获取高分书籍
            if high_rating_count > 0 and self._high_rating_books_cache:
                available_high = [
                    b
                    for b in self._high_rating_books_cache
                    if b["book_id"] not in exclude_set
                    and b["book_id"] not in used_book_ids
                ]
                selected_high = random.sample(
                    available_high, min(high_rating_count, len(available_high))
                )

                # 获取完整信息
                high_books = await self._get_books_with_categories(
                    [b["book_id"] for b in selected_high]
                )
                all_books.extend(high_books)
                used_book_ids.update(b["book_id"] for b in high_books)

            # 3. 获取随机书籍 - 使用OFFSET随机化
            if random_count > 0:
                random_books = await self._get_random_books_optimized(
                    random_count, exclude_set.union(used_book_ids)
                )
                all_books.extend(random_books)

            return all_books

        except Exception as e:
            print(f"获取随机书籍失败: {e}")
            return []

    async def _refresh_cache(self, min_rating_count: int, min_rating_score: float):
        """刷新候选池缓存"""
        pool = await self.db_connection.get_pool()

        try:
            async with pool.acquire() as conn:
                async with conn.cursor(aiomysql.DictCursor) as cursor:
                    # 获取热门书籍ID池（只查ID，提升速度）
                    hot_sql = """
                    SELECT book_id, reading_count
                    FROM books 
                    WHERE reading_count > 0 
                    ORDER BY reading_count DESC 
                    LIMIT 2000
                    """
                    await cursor.execute(hot_sql)
                    self._hot_books_cache = await cursor.fetchall()

                    # 获取高分书籍ID池
                    high_sql = """
                    SELECT book_id, rating, rating_count
                    FROM books 
                    WHERE rating_count >= %s AND rating >= %s
                    ORDER BY rating DESC, rating_count DESC 
                    LIMIT 2000
                    """
                    await cursor.execute(high_sql, (min_rating_count, min_rating_score))
                    self._high_rating_books_cache = await cursor.fetchall()

        except Exception as e:
            print(f"刷新缓存失败: {e}")

    async def _get_books_with_categories(
        self, book_ids: List[int]
    ) -> List[Dict[str, Any]]:
        """批量获取书籍完整信息（包含分类）"""
        if not book_ids:
            return []

        pool = await self.db_connection.get_pool()

        try:
            async with pool.acquire() as conn:
                async with conn.cursor(aiomysql.DictCursor) as cursor:
                    # 使用IN查询批量获取
                    placeholders = ",".join(["%s"] * len(book_ids))
                    sql = f"""
                    SELECT 
                        b.*,
                        GROUP_CONCAT(DISTINCT mc.main_category) as main_categories,
                        GROUP_CONCAT(DISTINCT sc.sub_category) as sub_categories
                    FROM books b
                    LEFT JOIN book_main_categories mc ON b.book_id = mc.book_id
                    LEFT JOIN book_sub_categories sc ON b.book_id = sc.book_id
                    WHERE b.book_id IN ({placeholders})
                    GROUP BY b.book_id
                    """

                    await cursor.execute(sql, book_ids)
                    results = await cursor.fetchall()
                    return self._process_book_results(results)

        except Exception as e:
            print(f"获取书籍信息失败: {e}")
            return []

    async def _get_random_books_optimized(
        self, count: int, exclude_ids: set
    ) -> List[Dict[str, Any]]:
        """优化的随机书籍获取 - 使用OFFSET替代RAND()"""
        pool = await self.db_connection.get_pool()

        try:
            async with pool.acquire() as conn:
                async with conn.cursor(aiomysql.DictCursor) as cursor:
                    # 先获取符合条件的总数 - 修复歧义问题
                    exclude_condition = ""
                    params = []
                    if exclude_ids:
                        placeholders = ",".join(["%s"] * len(exclude_ids))
                        exclude_condition = (
                            f"WHERE b.book_id NOT IN ({placeholders})"  # 添加表别名
                        )
                        params = list(exclude_ids)

                    # 修复计数查询，添加表别名
                    count_sql = (
                        f"SELECT COUNT(*) as total FROM books b {exclude_condition}"
                    )
                    await cursor.execute(count_sql, params)
                    total_result = await cursor.fetchone()
                    total_books = total_result["total"] if total_result else 0

                    if total_books == 0:
                        return []

                    # 使用随机OFFSET获取书籍
                    selected_books = []
                    attempts = 0
                    max_attempts = count * 3  # 避免无限循环

                    while len(selected_books) < count and attempts < max_attempts:
                        # 随机选择起始位置
                        random_offset = random.randint(0, max(0, total_books - 1))

                        # 修复SQL查询，确保没有歧义
                        sql = f"""
                        SELECT 
                            b.*,
                            GROUP_CONCAT(DISTINCT mc.main_category) as main_categories,
                            GROUP_CONCAT(DISTINCT sc.sub_category) as sub_categories
                        FROM books b
                        LEFT JOIN book_main_categories mc ON b.book_id = mc.book_id
                        LEFT JOIN book_sub_categories sc ON b.book_id = sc.book_id
                        {exclude_condition}
                        GROUP BY b.book_id
                        LIMIT 1 OFFSET %s
                        """

                        await cursor.execute(sql, params + [random_offset])
                        result = await cursor.fetchone()

                        if result and result["book_id"] not in [
                            b["book_id"] for b in selected_books
                        ]:
                            selected_books.append(result)

                        attempts += 1

                    return self._process_book_results(selected_books)

        except Exception as e:
            print(f"获取随机书籍失败: {e}")
            return []

    def _process_book_results(
        self, results: List[Dict[str, Any]]
    ) -> List[Dict[str, Any]]:
        """处理书籍查询结果，格式化分类信息"""
        processed_results = []

        for book in results:
            book_data = dict(book)

            # 处理主分类
            if book_data.get("main_categories"):
                book_data["main_categories"] = [
                    cat.strip()
                    for cat in book_data["main_categories"].split(",")
                    if cat.strip()
                ]
            else:
                book_data["main_categories"] = []

            # 处理子分类
            if book_data.get("sub_categories"):
                book_data["sub_categories"] = [
                    cat.strip()
                    for cat in book_data["sub_categories"].split(",")
                    if cat.strip()
                ]
            else:
                book_data["sub_categories"] = []

            processed_results.append(book_data)

        return processed_results

    async def clear_cache(self):
        """清除缓存，强制刷新"""
        self._cache_timestamp = 0
        self._hot_books_cache = []
        self._high_rating_books_cache = []
